﻿using Domain.Entity;
using DtoFw;
using DtoFw.Query;
using Repository;
using Service.Base;
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Util;
using DtoFw.Result;
using DtoFw.Enum;
using SqlSugar;
using Microsoft.AspNetCore.Http;
using System.IO;
using Cache;
using DtoFw.Base;
using DtoFw.Interface;
using System.Data;
using Microsoft.AspNetCore.Hosting;
using static Dapper.SqlMapper;
using Aspose.Cells.Drawing;

namespace Service
{
    public class EvidenceService : BaseService
    {
        private readonly CaseRepository _caseRepository;
        private readonly RoomBoxRepository _roomBoxRepository;
        private readonly RoomAreaRepository _roomAreaRepository;
        private readonly CasePhysicalEvidenceRepository _casePhysicalEvidenceRepository;
        private readonly CasePhysicalEvidenceTransfereRepository _casePhysicalEvidenceTransfereRepository;
        private readonly RoomCabinetRepository _roomCabinetRepository;
        private readonly IHostingEnvironment _hostingEnvironment;
        private readonly NotificationRepository _notificationRepository;

        public EvidenceService(CaseRepository caseRepository,
            CasePhysicalEvidenceRepository casePhysicalEvidenceRepository, MainSerialNumberRepository mainSerialNumberRepository,
            CasePhysicalEvidenceTransfereRepository casePhysicalEvidenceTransfereRepository, RoomBoxRepository roomBoxRepository,
            RoomAreaRepository roomAreaRepository, RoomCabinetRepository roomCabinetRepository,
            NotificationRepository notificationRepository, IHostingEnvironment hostingEnvironment
            ) : base(mainSerialNumberRepository)
        {
            this._caseRepository = caseRepository;
            this._casePhysicalEvidenceRepository = casePhysicalEvidenceRepository;
            this._casePhysicalEvidenceTransfereRepository = casePhysicalEvidenceTransfereRepository;
            this._roomBoxRepository = roomBoxRepository;
            this._roomAreaRepository = roomAreaRepository;
            this._roomCabinetRepository = roomCabinetRepository;
            this._notificationRepository = notificationRepository;
            _hostingEnvironment = hostingEnvironment;
        }
        /// <summary>
        /// 新增物品
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public BaseResult Post(EvidencePostReqDto reqDto)
        {
            var orderNumber = base.GetNumber(reqDto.UserId, DtoFw.Enum.EnumSystemNumber.WZBH);
            if (string.IsNullOrWhiteSpace(orderNumber))
                return BaseResult.Failure("未获取到物品序号");
            var entity = AutoMapperExtension.MapTo<EvidencePostReqDto, CasePhysicalEvidenceEntity>(reqDto);
            entity.No = orderNumber;
            entity.Creator = reqDto.UserName;
            entity.CreatorId = reqDto.UserId;
            var result = _casePhysicalEvidenceRepository.GetDb()
                .UseTran(() =>
                {
                    var id = _casePhysicalEvidenceRepository.Insert(entity);
                    if (id > 0)
                        _casePhysicalEvidenceRepository.GetDb().CommitTran();
                });
            if (!result.IsSuccess)
                return BaseResult.Failure("新增物品流水失败");
            return BaseResult.Succeed();
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public BaseResult Put(EvidencePutReqDto reqDto)
        {
            if (reqDto?.Id < 1)
                return BaseResult.Failure("参数错误");
            var entity = _casePhysicalEvidenceRepository.Get(new BaseQuery() { Id = reqDto.Id });
            if (entity == null)
                return BaseResult.Failure("物品对象为空");
            entity.Modifyer = reqDto.UserName;
            entity.ModifyerId = reqDto.UserId;
            entity = reqDto.MapTo<EvidencePutReqDto, CasePhysicalEvidenceEntity>(entity, true);
            _casePhysicalEvidenceRepository.Save(entity);
            return BaseResult.Succeed();
        }
        /// <summary>
        /// 修改物品RFID
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public BaseResult PutRFID(EvidenceRfidListReqDto reqDto)
        {
            if (reqDto == null || reqDto.list == null || reqDto.list.Count < 1)
                return BaseResult.Failure("参数错误");
            foreach (var item in reqDto.list)
            {
                var entities = _casePhysicalEvidenceRepository.GetDb().Queryable<CasePhysicalEvidenceEntity>()
                    .Where(x => x.Id == item.Id && !x.IsDeleted)?.ToList()
                      ?.Select(x =>
                      {
                          x.RFID = item?.RFID;
                          x.Modifyer = reqDto.UserName;
                          x.ModifyerId = reqDto.UserId;
                          x.OrganId = reqDto.OrganId;
                          return x;
                      })?.ToList();
                if (_casePhysicalEvidenceRepository.GetDb()
                    .Updateable<CasePhysicalEvidenceEntity>(entities).ExecuteCommand() > 0)
                    return BaseResult.Succeed();
            }

            //_notificationRepository
            //处理通知未处理
            _notificationRepository.HandleWarnEvidenceUnbound(reqDto.list.Select(v => v.Id).ToList(), "Admin");

            return BaseResult.Failure("操作失败");
        }
        /// <summary>
        /// 换位置
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public BaseGenericResult<List<OpenBoxDto>> PutLocation(EvidenceLocationPutReqDto putReqDto)
        {
            List<OpenBoxDto> boxDtos = new List<OpenBoxDto>();
            if (putReqDto == null || putReqDto.EvidenceIds.Count < 1 || putReqDto.LocationId < 1)
                return BaseGenericResult<List<OpenBoxDto>>.Failure(boxDtos, "参数错误");
            var db = _casePhysicalEvidenceTransfereRepository.GetDb();
            try
            {
                db.UseTran(() =>
                {
                    var deliveryNo = base.GetNumber(putReqDto.UserId, DtoFw.Enum.EnumSystemNumber.PZBH);
                    var list = db.Queryable<CasePhysicalEvidenceTransfereEntity>()
                       .Where(x => !x.IsDeleted && x.HistoryStatus == 0 && putReqDto.EvidenceIds.Contains(x.EvidenceId))?.ToList();
                    var upldateList = list.Select(x =>
                    {
                        x.HistoryStatus = 1;
                        x.Modifyer = putReqDto.UserName;
                        x.ModifyerId = putReqDto.UserId;
                        x.ModifyerTime = DateTime.Now;
                        return x;
                    })?.ToList();
                    db.Updateable<CasePhysicalEvidenceTransfereEntity>(upldateList).ExecuteCommand();
                    var mappings = AutoMapperExtension.MapTo<CasePhysicalEvidenceTransfereEntity, CasePhysicalEvidenceTransfereEntity>(list);
                    var insertTransfere = mappings.Select(x =>
                    {
                        x.Id = _casePhysicalEvidenceTransfereRepository.GetNext();
                        x.DeliveryNo = deliveryNo;
                        x.HistoryStatus = 0;
                        x.Remark = x.LocationName;
                        x.DeliveryDicType = 7;
                        x.DeliveryDicTypeName = "更换位置";
                        x.Location = putReqDto.LocationCode;
                        x.LocationId = putReqDto.LocationId;
                        x.Transferor = putReqDto.UserName;
                        x.TransferorOrgan = putReqDto.OrganName;
                        x.TransferorSignId = putReqDto.UserId;
                        x.Receiver = putReqDto.UserName;
                        x.ReceiverOrgan = putReqDto.OrganName;
                        x.ReceiverSignId = putReqDto.UserId;
                        x.CreateTime = x.ModifyerTime = DateTime.Now;
                        x.Creator = x.Modifyer;
                        x.CreatorId = x.ModifyerId;
                        x.Remark = x.Remark ?? "";
                        return x;
                    })?.ToList();
                    db.Insertable<CasePhysicalEvidenceTransfereEntity>(insertTransfere).ExecuteCommand();
                    db.Updateable<CasePhysicalEvidenceEntity>().SetColumns(x => new CasePhysicalEvidenceEntity()
                    {
                        Location = putReqDto.LocationCode,
                        Modifyer = putReqDto.UserName,
                        ModifyerId = putReqDto.UserId,
                        ModifyerTime = DateTime.Now,
                        OrganId = putReqDto.OrganId
                    }).Where(x => !x.IsDeleted && putReqDto.EvidenceIds.Contains(x.Id)).ExecuteCommand();
                    var total = mappings.Sum(x => x.EvidenceQuantity);
                    foreach (var item in list)
                    {
                        db.Updateable<RoomBoxEntity>().SetColumns(x => new RoomBoxEntity()
                        {
                            EviCount = x.EviCount > total ? x.EviCount - total : 0,
                            OrganId = putReqDto.OrganId,
                            Modifyer = putReqDto.UserName,
                            ModifyerId = putReqDto.UserId
                        }).Where(x => x.Id == item.LocationId && !x.IsDeleted).ExecuteCommand();
                    }
                    db.Updateable<RoomBoxEntity>().SetColumns(x => new RoomBoxEntity()
                    {
                        EviCount = x.EviCount + total,
                        OrganId = putReqDto.OrganId,
                        Modifyer = putReqDto.UserName,
                        ModifyerId = putReqDto.UserId
                    }).Where(x => x.Id == putReqDto.LocationId && !x.IsDeleted).ExecuteCommand();
                    db.CommitTran();
                    boxDtos = ChangeLocOpenBox(new OpenBoxDto()
                    {
                        boxid = list.Select(x => x.LocationId.Value).Distinct().ToList(),
                        tranNo = deliveryNo
                    }).Data;
                });
            }
            catch (Exception ex)
            {
                db.RollbackTran();
                return BaseGenericResult<List<OpenBoxDto>>.Failure(boxDtos, ex.Message);
            }
            return BaseGenericResult<List<OpenBoxDto>>.Succeed(boxDtos);
        }
        /// <summary>
        /// 更改位置开柜子 传拿出东西的柜子List<int> 待开柜子的tranNo
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public BaseGenericResult<List<OpenBoxDto>> ChangeLocOpenBox(OpenBoxDto openBoxDto)
        {
            List<OpenBoxDto> dtoList = new List<OpenBoxDto>();
            try
            {
                foreach (var item in openBoxDto.boxid)
                {
                    OpenBoxDto dto = new OpenBoxDto();
                    RoomBoxEntity boxentity = _roomBoxRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == item)).Single();
                    if (boxentity == null)
                        continue;

                    RoomCabinetEntity cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == boxentity.CabinetId)).Single();
                    if (cabinetEntity == null || string.IsNullOrWhiteSpace(cabinetEntity.Addr))
                        continue;

                    GetOpenBox(dtoList, dto, boxentity, cabinetEntity);
                }

                var trans = _roomBoxRepository.GetDb().Queryable<CasePhysicalEvidenceTransfereEntity>()
                      .Where(v => v.DeliveryNo == openBoxDto.tranNo).ToList();

                List<long?> list = trans.Select(v => v.LocationId).Distinct().ToList();

                foreach (var item in list)
                {
                    if (item.HasValue)
                    {
                        long id = item.Value;
                        OpenBoxDto dto = new OpenBoxDto();
                        RoomBoxEntity boxentity = _roomBoxRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == id)).Single();
                        if (boxentity == null)
                            continue;

                        RoomCabinetEntity cabinetEntity = _roomCabinetRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == boxentity.CabinetId)).Single();
                        if (cabinetEntity == null || string.IsNullOrWhiteSpace(cabinetEntity.Addr))
                            continue;

                        GetOpenBox(dtoList, dto, boxentity, cabinetEntity);
                    }
                }


                if (dtoList.Count > 0)
                    return BaseGenericResult<List<OpenBoxDto>>.Succeed(dtoList);
                else
                    return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");

            }
            catch (Exception)
            {
                return BaseGenericResult<List<OpenBoxDto>>.Failure(dtoList, "您选择的不是智能柜，不需要开柜");
            }
        }

        private void GetOpenBox(List<OpenBoxDto> dtoList, OpenBoxDto dto, RoomBoxEntity boxentity, RoomCabinetEntity cabinetEntity)
        {
            dto.ComNo = cabinetEntity.Addr;
            //密集柜
            if (cabinetEntity.CabinetCode == "1" && cabinetEntity.Smart.Value)
            {
                RoomAreaEntity areaEntity = _roomAreaRepository.Query(new BaseQuery() { }, x => x.Where(x => x.Id == cabinetEntity.AreaId)).Single();
                dto.CabinetType = 1;
                dto.AreaNo = areaEntity.WarehouseNo;
                dto.ColumnNo = boxentity.ColNO.Value;
                if (boxentity.Direction == 0 && cabinetEntity.FixedColumn == "1")
                {
                    dto.ColumnNo = boxentity.ColNO.Value + 1;
                }
                else if (boxentity.Direction == 1 && cabinetEntity.FixedColumn == "2")
                {
                    dto.ColumnNo = boxentity.ColNO.Value + 1;
                }
                dto.FixedColumn = cabinetEntity.FixedColumn;

                if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.ColumnNo == dto.ColumnNo && v.FixedColumn == dto.FixedColumn).Any())
                    dtoList.Add(dto);
            }
            else if (cabinetEntity.CabinetCode == "2" && cabinetEntity.Smart.Value) //智能柜
            {
                //58 01 02 03 ff 00 80 8B
                dto.CabinetType = 2;
                dto.Module = cabinetEntity.ModuleNo;
                dto.Port = boxentity.Port.Value;
                dtoList.Add(dto);
                if (!dtoList.Where(v => v.ComNo == dto.ComNo && v.AreaNo == dto.AreaNo && v.Module == dto.Module && v.Port == dto.Port).Any())
                    dtoList.Add(dto);
            }

        }
        /// <summary>
        /// 删除物品
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public BaseResult Delete(DeleteDto reqDto)
        {
            if (_casePhysicalEvidenceRepository.GetDb().Queryable<CasePhysicalEvidenceEntity>()
                    .Where(x => reqDto.Ids.Contains(x.Id) && !x.IsDeleted && x.Status == 1).Any())
            {
                return BaseResult.Failure("不能删除已入库物品");
            }
            var dellist = _casePhysicalEvidenceRepository.Query(new BaseQuery(),
                  t => t.Where(m => reqDto.Ids.Contains(m.Id)))?.ToList()?
                  .Select(x =>
                  {
                      x.IsDeleted = true;
                      return x;
                  })?.ToList();



            //if (_casePhysicalEvidenceRepository.GetDb().Deleteable(dellist).ExecuteCommand() > 0)
            {
                foreach (var item in reqDto.Ids)
                {
                    _caseRepository.GetDb().Updateable<CasePhysicalEvidenceEntity>().SetColumns(x => new CasePhysicalEvidenceEntity() { IsDeleted = true })
                    .Where(x => x.Id == item).ExecuteCommand();
                    //删除交接记录
                    _casePhysicalEvidenceRepository.DeleteTrans(item);
                }
                return BaseResult.Succeed();
            }
            return BaseResult.Failure("操作失败");
        }
        /// <summary>
        /// 案件物品分页
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public Pager<CaseEvidencePagerRespDto> QueryCaseEvidences(EvidenceQueryReqDto reqDto)
        {
            return _casePhysicalEvidenceRepository.QueryCaseEvidences(reqDto);
        }
        /// <summary>
        /// 案件物品分页
        /// </summary>
        /// <param name="reqDto"></param>
        /// <returns></returns>
        public Pager<CaseEvidencePagerRespDto> QueryCaseEvidencesByLocationId(EvidenceLocationQueryReqDto reqDto)
        {
            return _casePhysicalEvidenceRepository.QueryCaseEvidences(reqDto);
        }
        /// <summary>
        /// 获取单个物品
        /// </summary>
        /// <param name="evidenceId"></param>
        /// <returns></returns>
        public BaseGenericResult<EvidenceRespDto> Get(long evidenceId)
        {
            var entity = _casePhysicalEvidenceRepository.Get(new BaseQuery() { Id = evidenceId });
            var resp = AutoMapperExtension.MapTo<CasePhysicalEvidenceEntity, EvidenceRespDto>(entity);
            if (entity == null)
                return BaseGenericResult<EvidenceRespDto>.Failure(resp, "操作异常");
            return BaseGenericResult<EvidenceRespDto>.Succeed(resp);
        }
        /// <summary>
        /// 获取案件物品
        /// </summary>
        /// <param name="evidenceId"></param>
        /// <returns></returns>
        public BaseGenericResult<List<EvidenceRespDto>> GetCaseBorrowEvdences(CaseEvidenceQueryReqDto reqDto)
        {
            var list = _casePhysicalEvidenceRepository.GetDb().Queryable<CasePhysicalEvidenceEntity>()
                .WhereIF(reqDto.Status != EnumEvidenceStatus.WZ, x => x.Status == (int)reqDto.Status)
                .Where(x => x.CaseId == reqDto.CaseId && !x.IsDeleted)?.ToList();
            var resp = AutoMapperExtension.MapTo<CasePhysicalEvidenceEntity, EvidenceRespDto>(list);
            return BaseGenericResult<List<EvidenceRespDto>>.Succeed(resp);
        }
        /// <summary>
        /// 获取物品类别数据
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<EvidenceClassRespDto> GetEvidenceClass()
        {
            EvidenceClassRespDto evidenceClassRespDto = new EvidenceClassRespDto();
            evidenceClassRespDto.list = _casePhysicalEvidenceRepository.GetDb()
                .Queryable<CasePhysicalEvidenceEntity>()
                .Where(x => !x.IsDeleted && !string.IsNullOrWhiteSpace(x.CategoryName))
           .GroupBy(x => new { x.ClassName, x.CategoryName })
           .Select(x => new EvidenceClassResp()
           {
               pname = x.ClassName,
               name = x.CategoryName,
               value = SqlFunc.AggregateSum(x.Quantity)
           })?.ToList();

            foreach (var item in evidenceClassRespDto.list)
            {
                item.name = $"{item.pname}-{item.name}";
            }
            return BaseGenericResult<EvidenceClassRespDto>.Succeed(evidenceClassRespDto);
        }
        /// <summary>
        /// 月度物品流转情况
        /// </summary>
        /// <returns></returns>
        public BaseGenericResult<MonthEvidenceTransfereRespDto> GetMonthEvidenceTransfere()
        {
            MonthEvidenceTransfereRespDto list = new MonthEvidenceTransfereRespDto();
            list.Months = new List<string>() { "1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月" };
            list.Names = new List<string>() { "已入库", "已出库", "已借调", "已移交", "已送检" };
            list.list = new List<MonthEvidenceTransfereResp>();
            var list1 = _casePhysicalEvidenceRepository.GetDb()
                  .Queryable<CaseEntity, CasePhysicalEvidenceEntity, CasePhysicalEvidenceTransfereEntity>
                ((t1, t2, t3) => new JoinQueryInfos(
                   JoinType.Left, t1.Id == t2.CaseId, JoinType.Left, t2.Id == t3.EvidenceId))
                  .Where((t1, t2, t3) => !t1.IsDeleted && !t2.IsDeleted)
                  .Where((t1, t2, t3) => t1.CaseTime != null)
                  //.OrderBy((t1, t2, t3) => t1.CaseTime)
                  .Select((t1, t2, t3) => new MonthEvidece()
                  {
                      Status = t3.EvidenceStatus ?? 0,
                      Quantity = t2.Quantity,
                      DeliveryDicType = t3.DeliveryDicType,
                      EvidenceQuantity = t3.EvidenceQuantity,
                      CreateTime = t1.CaseTime
                  })?.ToList();
            var tupl = getMonth();
            MonthEvidenceTransfereResp yrk = new MonthEvidenceTransfereResp();
            yrk.Name = "已入库";
            yrk.list = new List<int>();
            foreach (var item in tupl)
                yrk.list.Add(list1.Count(x => x.Status == 1 && x.CreateTime > item.startTime && x.CreateTime < item.endTime));
            MonthEvidenceTransfereResp yck = new MonthEvidenceTransfereResp();
            yck.Name = "已出库";
            yck.list = new List<int>();
            foreach (var item in tupl)
                yck.list.Add(list1.Count(x => x.Status == 2 && x.CreateTime > item.startTime && x.CreateTime < item.endTime));
            MonthEvidenceTransfereResp yjd = new MonthEvidenceTransfereResp();
            yjd.Name = "已借调";
            yjd.list = new List<int>();
            foreach (var item in tupl)
                yjd.list.Add(list1.Count(x => x.Status == 2 && x.DeliveryDicType == 1 && x.CreateTime > item.startTime && x.CreateTime < item.endTime));
            MonthEvidenceTransfereResp yyj = new MonthEvidenceTransfereResp();
            yyj.Name = "已移交";
            yyj.list = new List<int>();
            foreach (var item in tupl)
                yyj.list.Add(list1.Count(x => x.Status == 2 && x.DeliveryDicType == 5 && x.CreateTime > item.startTime && x.CreateTime < item.endTime));
            MonthEvidenceTransfereResp ysj = new MonthEvidenceTransfereResp();
            ysj.Name = "已送检";
            ysj.list = new List<int>();
            foreach (var item in tupl)
                ysj.list.Add(list1.Count(x => x.Status == 2 && x.DeliveryDicType == 2 && x.CreateTime > item.startTime && x.CreateTime < item.endTime));
            list.list.Add(yrk);
            list.list.Add(yck);
            list.list.Add(yjd);
            list.list.Add(yyj);
            list.list.Add(ysj);
            return BaseGenericResult<MonthEvidenceTransfereRespDto>.Succeed(list);
        }
        /// <summary>
        /// 获取月日期
        /// </summary>
        /// <returns></returns>
        private List<(string month, DateTime startTime, DateTime endTime)> getMonth()
        {
            DateTime year = new DateTime(DateTime.Now.Year, 1, 1);
            //1月
            DateTime month1Start = year;
            DateTime month1End = month1Start.AddMonths(1).AddSeconds(-1);
            //2月
            DateTime month2Start = month1End.AddSeconds(1);
            DateTime month2End = month2Start.AddMonths(1).AddSeconds(-1);
            //3月
            DateTime month3Start = month2End.AddSeconds(1);
            DateTime month3End = month3Start.AddMonths(1).AddSeconds(-1);
            //4月
            DateTime month4Start = month3End.AddSeconds(1);
            DateTime month4End = month4Start.AddMonths(1).AddSeconds(-1);
            //5月
            DateTime month5Start = month4End.AddSeconds(1);
            DateTime month5End = month5Start.AddMonths(1).AddSeconds(-1);
            //6月
            DateTime month6Start = month5End.AddSeconds(1);
            DateTime month6End = month6Start.AddMonths(1).AddSeconds(-1);
            //7月
            DateTime month7Start = month6End.AddSeconds(1);
            DateTime month7End = month7Start.AddMonths(1).AddSeconds(-1);
            //8月
            DateTime month8Start = month7End.AddSeconds(1);
            DateTime month8End = month8Start.AddMonths(1).AddSeconds(-1);
            //9月
            DateTime month9Start = month8End.AddSeconds(1);
            DateTime month9End = month9Start.AddMonths(1).AddSeconds(-1);
            //10月
            DateTime month10Start = month9End.AddSeconds(1);
            DateTime month10End = month10Start.AddMonths(1).AddSeconds(-1);
            //11
            DateTime month11Start = month10End.AddSeconds(1);
            DateTime month11End = month11Start.AddMonths(1).AddSeconds(-1);
            //12
            DateTime month12Start = month11End.AddSeconds(1);
            DateTime month12End = month12Start.AddMonths(1).AddSeconds(-1);
            List<(string month, DateTime startTime, DateTime endTime)> tulp = new List<(string month, DateTime startTime, DateTime endTime)>()
            {
                ("1月",month1Start,month1End),
                ("2月",month2Start,month2End),
                ("3月",month3Start,month3End),
                ("4月",month4Start,month4End),
                ("5月",month5Start,month5End),
                ("6月",month6Start,month6End),
                ("7月",month7Start,month7End),
                ("8月",month8Start,month8End),
                ("9月",month9Start,month9End),
                ("10月",month10Start,month10End),
                ("11月",month11Start,month11End),
                ("12月",month12Start,month12End),
            };
            return tulp;
        }

        public BaseGenericResult<string> UploadEvidences(TokenData reqDto, FileInfo fileInfo, Byte[] byts, long caseID)
        {
            string path = _hostingEnvironment.ContentRootPath.Replace("\\", "/");
            var url = $"/File/Evidence/";
            if (!System.IO.Directory.Exists(path + url))
            {
                System.IO.Directory.CreateDirectory(path + url);
            }
            url = $"{url}{DateTime.Now.ToString("yyyyMMddHHssfff")}{fileInfo.Extension}";
            File.WriteAllBytes(path + url, byts);

            DataSet ds = Aspose(path + url);

            if (ds is null)
                return new BaseGenericResult<string>() { Success = false, Msg = "操作失败,excel格式错误" };

            DataTable dt = ds.Tables[0];

            List<CasePhysicalEvidenceEntity> cList = new List<CasePhysicalEvidenceEntity>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (i >= 201)
                    break;

                var entity = new CasePhysicalEvidenceEntity();
                entity.CaseId = caseID;
                entity.Name = dt.Rows[i][1].ToString(); // 物品名称 name
                if (string.IsNullOrWhiteSpace(entity.Name))
                    continue;

                entity.ExtractMethod = dt.Rows[i][2].ToString(); // 获取方式 extractMethod
                entity.Source = dt.Rows[i][3].ToString(); //  规格型号 source
                if (int.TryParse(dt.Rows[i][4].ToString(), out int result)) //  数量 quantity
                    entity.Quantity = result;//数量
                entity.Unit = dt.Rows[i][5].ToString(); //    计量单位 unit

                entity.ClassName = dt.Rows[i][6].ToString(); //     物品类别1 class
                entity.CategoryName = dt.Rows[i][7].ToString(); //    物品类别2 category
                entity.SubCategoryName = dt.Rows[i][8].ToString(); //   物品类别3 subCategoryId
                MainDicEntity dicp1 = default;
                MainDicEntity dicp2 = default;
                MainDicEntity dicp3 = default;
                if (!string.IsNullOrWhiteSpace(entity.ClassName))
                {
                    dicp1 = _casePhysicalEvidenceRepository.GetDb().Queryable<MainDicEntity>().First(x => x.Name == entity.ClassName && x.Remark == "物品分类");
                    if (dicp1 != null)
                        entity.Class = dicp1.Code;
                }

                if (!string.IsNullOrWhiteSpace(entity.CategoryName))
                {
                    dicp2 = _casePhysicalEvidenceRepository.GetDb().Queryable<MainDicEntity>().First(x => x.Name == entity.CategoryName && x.ParentId == dicp1.Id);
                    if (dicp2 != null)
                        entity.Category = dicp2.Id.ToString();
                }

                if (!string.IsNullOrWhiteSpace(entity.SubCategoryName))
                {
                    dicp3 = _casePhysicalEvidenceRepository.GetDb().Queryable<MainDicEntity>().First(x => x.Name == entity.SubCategoryName && x.ParentId == dicp2.Id);
                    if (dicp3 != null)
                        entity.SubCategoryId = dicp3.Id.ToString();
                }

                if (int.TryParse(dt.Rows[i][9].ToString(), out int eresult)) //    重量（kg）extractionOrganId
                    entity.ExtractionOrganId = eresult;

                entity.ExtractionPersonName = dt.Rows[i][10].ToString(); //  颜色 extractionPersonName
                entity.ChengSe = dt.Rows[i][11].ToString(); //  成色 chengSe
                if (DateTime.TryParse(dt.Rows[i][12].ToString(), out DateTime dresult)) //    强制措施时间 caiQuChuoSiDate
                    entity.CaiQuChuoSiDate = dresult;

                entity.ExtractionParts = dt.Rows[i][13].ToString(); //  物品特征 describe
                entity.Describe = dt.Rows[i][14].ToString(); //  物品特征 describe
                entity.Shape = dt.Rows[i][15].ToString(); //  物品属性 shapeLs
                entity.Remark = dt.Rows[i][16].ToString(); //  备注 remark

                entity.ModifyerId = entity.CreatorId = reqDto?.UserId;
                entity.Modifyer = entity.Creator = reqDto?.UserName;
                entity.ModifyerTime = entity.CreateTime = DateTime.Now;
                entity.OrganId = reqDto.OrganId;
                entity.OrganName = reqDto.OrganName;
                entity.DeptId = reqDto.DeptId;
                entity.ComNo = reqDto.ComNo;
                entity.UserId = reqDto.UserId;

                _casePhysicalEvidenceRepository.Save(entity);
            }


            return new BaseGenericResult<string>() { Success = true, Msg = "导入成功" };

        }

        /// <summary>
        /// 导入4(Aspose)
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet Aspose(string filePath)
        {
            DataSet ds = null;
            try
            {
                Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(filePath);

                ds = new DataSet();
                DataTable dt = null;
                int rowIndex = 0;
                int colIndex = 0;
                for (int i = 0; i < workbook.Worksheets.Count; i++)
                {
                    dt = new DataTable();
                    dt.TableName = "table" + i.ToString();
                    Aspose.Cells.Worksheet worksheet = workbook.Worksheets[i];
                    //获取每个sheet表的所有单元格
                    Aspose.Cells.Cells cells = worksheet.Cells;
                    dt = cells.ExportDataTableAsString(rowIndex, colIndex, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
                    ds.Tables.Add(dt);

                    break;
                }
            }
            catch (Exception ex)
            {
                ds = null;
            }
            return ds;
        }
    }
}
